﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Data;
using System.Text;
using System.Windows.Forms;
using DevExpress.XtraEditors;
using ERP_RORZE_PRODUCT.Product.DAO;
using System.Data.OleDb;
using ERP_RORZE_SYSTEM;
using System.Data.Common;
using System.Data.SqlClient;

namespace ERP_RORZE_PRODUCT.Product.LINK_VCUONGDATABASE
{
    public partial class ctrlKHKT06 : DevExpress.XtraEditors.XtraUserControl
    {
        DataTable dt;
        ClsLinkVCuongDAO clsLVDAO = new ClsLinkVCuongDAO();

        public ctrlKHKT06()
        {
            InitializeComponent();
        }

        private void ctrlKHKT06_Load(object sender, EventArgs e)
        {
            dt = clsLVDAO.GetAllTnhapOrder();
            gridControl1.DataSource = dt;
            gridView1.BestFitColumns();
        }

        private void simpleButton3_Click(object sender, EventArgs e)
        {
            gridView1.PopulateColumns();
            dt = clsLVDAO.GetAllDHMN();
            gridControl1.DataSource = dt;
            gridView1.BestFitColumns();
        }

        private void simpleButton2_Click(object sender, EventArgs e)
        {
            gridView1.PopulateColumns();
            dt = clsLVDAO.GetAllKHKT06();
            gridControl1.DataSource = dt;
            gridView1.BestFitColumns();
        }

        private void btnRefresh_Click(object sender, EventArgs e)
        {
            gridView1.PopulateColumns();
            dt = clsLVDAO.GetAllTnhapOrder();
            gridControl1.DataSource = dt;
            gridView1.BestFitColumns();
        }

        private void simpleButton1_Click(object sender, EventArgs e)
        {
            FileDialog fileDialog = new OpenFileDialog();
            fileDialog.Filter = "Excel 2007-2013|*.xlsx|Excel 97-2003 (*.xls)|*.xls";
            if (fileDialog.ShowDialog() == DialogResult.OK)
            {
                OleDbConnection connection = new OleDbConnection();
                try
                {
                    string path = fileDialog.FileName;
                    INISettings iniSettings = new INISettings();
                    string connectString = iniSettings.SQLConnectionString;
                    // Connection String to Excel Workbook
                    string excelConnectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 12.0;Persist Security Info=False", path);

                    connection.ConnectionString = excelConnectionString;
                    OleDbCommand command = new OleDbCommand("select * from [Sheet1$]", connection);
                    connection.Open();
                    // Create DbDataReader to Data Worksheet
                    DbDataReader dr = command.ExecuteReader();

                    // Bulk Copy to SQL Server 
                    SqlBulkCopy bulkInsert = new SqlBulkCopy(connectString);
                    bulkInsert.DestinationTableName = "[rrc_database].[dbo].[242_Tnhaporder]";

                    SqlBulkCopyColumnMapping date = new SqlBulkCopyColumnMapping("InputDate", "InputDate");
                    bulkInsert.ColumnMappings.Add(date);

                    SqlBulkCopyColumnMapping toolId = new SqlBulkCopyColumnMapping("Order", "Order");
                    bulkInsert.ColumnMappings.Add(toolId);

                    bulkInsert.WriteToServer(dr);

                    connection.Close();
                    btnRefresh_Click(sender, e);
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                    connection.Close();
                }
            }
        }

        private void btnImportnew_Click(object sender, EventArgs e)
        {
            FileDialog fileDialog = new OpenFileDialog();
            fileDialog.Filter = "Excel 2007-2013|*.xlsx|Excel 97-2003 (*.xls)|*.xls";
            if (fileDialog.ShowDialog() == DialogResult.OK)
            {
                OleDbConnection connection = new OleDbConnection();
                try
                {
                    string path = fileDialog.FileName;
                    INISettings iniSettings = new INISettings();
                    string connectString = iniSettings.SQLConnectionString;
                    // Connection String to Excel Workbook
                    string excelConnectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 12.0;Persist Security Info=False", path);

                    connection.ConnectionString = excelConnectionString;
                    OleDbCommand command = new OleDbCommand("select * from [Sheet1$]", connection);
                    connection.Open();
                    // Create DbDataReader to Data Worksheet
                    DbDataReader dr = command.ExecuteReader();

                    // Bulk Copy to SQL Server 
                    SqlBulkCopy bulkInsert = new SqlBulkCopy(connectString);
                    bulkInsert.DestinationTableName = "[rrc_database].[dbo].[242_XTDonhangmuangoai]";

                    SqlBulkCopyColumnMapping date = new SqlBulkCopyColumnMapping("Order", "Order");
                    bulkInsert.ColumnMappings.Add(date);

                    SqlBulkCopyColumnMapping toolId = new SqlBulkCopyColumnMapping("PartNo", "PartNo");
                    bulkInsert.ColumnMappings.Add(toolId);

                    SqlBulkCopyColumnMapping Moqty = new SqlBulkCopyColumnMapping("Moqty", "Moqty");
                    bulkInsert.ColumnMappings.Add(Moqty);

                    SqlBulkCopyColumnMapping Deadline = new SqlBulkCopyColumnMapping("Deadline", "Deadline");
                    bulkInsert.ColumnMappings.Add(Deadline);

                    SqlBulkCopyColumnMapping Sup = new SqlBulkCopyColumnMapping("Sup", "Sup");
                    bulkInsert.ColumnMappings.Add(Sup);

                    bulkInsert.WriteToServer(dr);

                    connection.Close();
                    btnRefresh_Click(sender, e);
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                    connection.Close();
                }
            }
        }
    }
}
